#!/usr/bin/env python
# -*- coding:utf-8 -*-

import pymysql

"""
try:
    conn = pymysql.connect(
    host = '18.18.23.2',
    port = 3310,
    user = 'root',
    passwd ='123.cn',
    db = 'school',
    )
    print(conn.ping(False))
finally:
    if conn:
        conn.close()
    print(conn.ping(False))
"""




# 使用insert into语句插入数据
"""
try:
    conn = pymysql.connect(
    host = '18.18.23.2',
    port = 3310,
    user = 'root',
    passwd ='123.cn',
    db = 'school',
    )
    print(conn.ping(False))    
    cursor = conn.cursor()
    insert_sql = "insert into student(name,age) values('tom',20)"

    rows = cursor.execute(insert_sql)
    print(rows)

finally:
    if conn:
        conn.close()
# 注意: 在数据库中该数据并没有提交成功. 因为咱们没有设置自动提交.
# 一般需要手动管理事务.
"""




# 事务管理
"""
Connection类有三个方法:
    - begin 开始事务
    - commit 将变更提交.
    - rollback 回滚事务.
"""
"""
conn = pymysql.connect(
    host = '18.18.23.2',
    port = 3310,
    user = 'root',
    passwd ='123.cn',
    db = 'school',
)
cursor = conn.cursor()
try:
    insert_sql = "insert into student(name,age)values('summar',20)"
    rows = cursor.execute(insert_sql)
    print(rows)
    conn.commit()
except:
    conn.rollback()
finally:
    if cursor:
        cursor.close()
    if conn:
        conn.close()
"""




# 批量增加数据
"""
conn = pymysql.connect(

    host = '18.18.23.2',
    port = 3310,
    user = 'root',
    passwd ='123.cn',
    db = 'school',
)
cursor = conn.cursor()
try:
    for i in range(10):
        insertsql = "insert into student(name,age)values('jacky{0}',20+{0})".format(i)
        rows = cursor.execute(insertsql)
    conn.commit()
except:
    conn.rollback()
finally:
    if cursor:
        cursor.close()
    if conn:
        conn.close()
"""





"""
一般流程
    - 建立连接
    - 获取游标
    - 执行SQL
    - 提交事务
    - 释放资源.
查询:
    Cursor类的获取查询结果集的方法有
    fetchone(),fetchmany(size=None), fetchall().
"""
"""
conn = pymysql.connect(
    host = '18.18.23.2',
    port = 3310,
    user = 'root',
    passwd ='123.cn',
    db = 'school',
)
cursor = conn.cursor()

sql = 'select * from student'
rows = cursor.execute(sql) # 返回影响的行数.

print(cursor.fetchone())
print(cursor.fetchone())
print('-'*20)
print(cursor.fetchmany(2))
print('+'*20)
print(cursor.fetchmany(2))
print('='*20)
print(cursor.fetchall())

if cursor:
    cursor.close()
if conn:
    conn.close()
"""




# SQL注入攻击
# 找出某个id对应用户的信息
"""
conn = pymysql.connect(
    host = '18.18.23.2',
    port = 3310,
    user = 'root',
    passwd ='123.cn',
    db = 'school',
)
cursor = conn.cursor()

userid = 5 # 用户id
# userid可以变,例如从客户端request请求中获取,直接拼接到查询字符串中.
sql = 'SELECT * from student WHERE id = {}'.format(userid)

rows = cursor.execute(sql) # 返回影响的行数.

print(cursor.fetchone())

if cursor:
    cursor.close()
if conn:
    conn.close()
"""



# 参数化查询
# 参数化查询: 可以有效防止注入攻击,并提高查询的效率.
"""
import pymysql
from pymysql.cursors import DictCursor

conn = pymysql.connect(
    host = '18.18.23.2',
    port = 3310,
    user = 'root',
    passwd ='123.cn',
    db = 'school',
)
cursor = conn.cursor(DictCursor)

userid = '5 or 1=1'
sql = 'SELECT * FROM student WHERE id = %s'
cursor.execute(sql,(userid,)) # 参数化查询
print(cursor.fetchall())
print('~'*30)
sql = 'SELECT * from student WHERE name like %(name)s and age > %(age)s'
cursor.execute(sql, {'name':'jacky%','age':25}) # 参数化查询
print(cursor.fetchall())

if cursor:
    cursor.close()
if conn:
    conn.close()
"""



# 上下文支持
"""
conn = pymysql.connect(
    host = '18.18.23.2',
    port = 3310,
    user = 'root',
    passwd ='123.cn',
    db = 'school',
)
try:
    with conn.cursor() as cursor:
        for i in range(3):
            insertsql = "insert into student(name,age)values('jacky{0}',20+{0})".format(i)
            rows = cursor.execute(insertsql)
    conn.commit()
except Exception as e:
    print(e)
    conn.rollback()
finally:
    conn.close()
"""






# 换一种写法,使用连接的上下文
"""
conn = pymysql.connect(
    host = '18.18.23.2',
    port = 3310,
    user = 'root',
    passwd ='123.cn',
    db = 'school',
)

with conn as cursor:
    for i in range(3):
        insertsql = "insert into student(name,age)values('jacky{0}',30+{0})".format(i)
        rows = cursor.execute(insertsql)

sql = "select * from student"
cursor.execute(sql)
print(cursor.fetchall())

# 关闭
cursor.close()
conn.close()
"""
"""
conn的with进入是返回一个新的cursor对象,退出时,只是提交或者回滚了事务.并没有关闭cursor和conn.
不关闭cursor就可以接着用,省的反复创建它.
"""


# 关闭cursor对象
conn = pymysql.connect(
    host = '18.18.23.2',
    port = 3310,
    user = 'root',
    passwd ='123.cn',
    db = 'school',
)

with conn as cursor:
    with cursor:
        sql = "select * from student"
        cursor.execute(sql)
        print(cursor.fetchall())

# 关闭
conn.close()

# 通过上面的脚本,知道了:
# 连接应该不需要反反复复创建销毁,应该是多个cursor共享一个conn.
